from openpyxl.utils import range_boundaries
from openpyxl.worksheet.worksheet import Worksheet

from .schema import (
    SummaryProduct,
    BidProduct,
    Offer,
    ProductDetail,
    OfferDetail,
    SummaryTable,
    BidTable,
    START_ROW_OF_DATA,
    ExceptionOfferDetail,
    ExceptionOffer,
    NO_UNIT_PRICE_STATUS,
)

# 新表头结构常量
COL_CATEGORY = 0  # A列：类别
COL_CODE = 1  # B列：存货编码
COL_NAME = 2  # C列：存货名称
COL_SPEC = 3  # D列：规格型号
COL_UNIT = 4  # E列：单位
COL_QUANTITY = 5  # F列：采购申请数量
COL_PRICE = 6  # G列：报价
COL_TOTAL = 7  # H列：总价
COL_REMARK = 8  # I列：备注（品牌）


def import_products_from_tender(sheet: Worksheet) -> SummaryTable:
    """从招标文件导入产品数据（新表头结构）"""
    table = SummaryTable(table_name=sheet.title)

    # 预处理合并单元格
    unmerge_and_fill_cells(sheet)

    for row in sheet.iter_rows(min_row=START_ROW_OF_DATA, max_row=sheet.max_row):
        # 检查必要字段是否为空
        if not all([row[COL_NAME].value, row[COL_SPEC].value]):
            continue

        detail = ProductDetail(
            category=row[COL_CODE].value,  # 使用存货编码作为序号
            name=row[COL_NAME].value,
            spec=row[COL_SPEC].value,
            unit=row[COL_UNIT].value,
            code=row[COL_CODE].value,  # 正式存货编码
            quantity=row[COL_QUANTITY].value,
            # 新表头不再包含理论重量字段
        )

        try:
            product = SummaryProduct(
                key_name=detail.name,
                key_spec=detail.spec,
                detail=detail,
                offers=[],  # 汇总表需要后续添加报价
            )
            table.products.append(product)
        except Exception as e:
            print(f"Error creating product at row {row[0].row}: {e}")

    return table


def import_products_from_bid(
    sheet: Worksheet, supplier: str, is_bargaining: bool = False
) -> BidTable:
    """从投标文件导入产品数据（新表头结构）"""
    table = BidTable(table_name=sheet.title, supplier=supplier)

    # 预处理合并单元格
    unmerge_and_fill_cells(sheet)

    for row in sheet.iter_rows(min_row=START_ROW_OF_DATA, max_row=sheet.max_row):
        # 检查必要字段是否为空
        if not all([row[COL_NAME].value, row[COL_SPEC].value]):
            continue

        detail = ProductDetail(
            order_num=row[COL_CODE].value,  # 使用存货编码作为序号
            name=row[COL_NAME].value,
            spec=row[COL_SPEC].value,
            unit=row[COL_UNIT].value,
            code=row[COL_CODE].value,  # 正式存货编码
            quantity=row[COL_QUANTITY].value,
        )

        price_value = row[COL_PRICE].value
        total_value = row[COL_TOTAL].value
        brand_remark = row[COL_REMARK].value

        # 处理异常报价情况
        if price_value in NO_UNIT_PRICE_STATUS:
            exception_detail = ExceptionOfferDetail(
                unit_price=price_value,
                total_price=total_value,
                brand=brand_remark,  # 备注字段包含品牌信息
                remark=brand_remark,
            )
            offer = ExceptionOffer(
                status=0,  # 无报价
                supplier=supplier,
                key_price=price_value,
                is_bargaining=is_bargaining,
                detail=exception_detail,
            )
        elif not isinstance(price_value, (int, float)):
            exception_detail = ExceptionOfferDetail(
                unit_price=price_value,
                total_price=total_value,
                brand=brand_remark,
                remark=brand_remark,
            )
            offer = ExceptionOffer(
                status=-1,  # 报价异常
                supplier=supplier,
                key_price=price_value,
                is_bargaining=is_bargaining,
                detail=exception_detail,
            )
        else:
            # 正常报价
            offer_detail = OfferDetail(
                unit_price=price_value,
                total_price=total_value,
                brand=brand_remark,
                remark=brand_remark,
            )
            offer = Offer(
                supplier=supplier,
                key_price=price_value,
                is_bargaining=is_bargaining,
                detail=offer_detail,
            )

        product = BidProduct(
            key_name=detail.name, key_spec=detail.spec, detail=detail, offer=offer
        )
        table.products.append(product)

    return table


# 保留原有的合并单元格处理函数
def unmerge_and_fill_cells(worksheet):
    """拆分合并单元格并填充值"""
    merged_ranges = list(worksheet.merged_cells.ranges)
    for merged_range in merged_ranges:
        min_col, min_row, max_col, max_row = range_boundaries(str(merged_range))
        merged_value = worksheet.cell(row=min_row, column=min_col).value
        worksheet.unmerge_cells(str(merged_range))
        for row in range(min_row, max_row + 1):
            for col in range(min_col, max_col + 1):
                worksheet.cell(row=row, column=col).value = merged_value
